package at.fhj.antesk.steamvaluecalc;

import java.io.IOException;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Vector;

import javax.swing.JOptionPane;
import javax.swing.JProgressBar;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableModel;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import org.apache.commons.lang3.exception.ExceptionUtils;
import org.jsoup.Connection;
import org.jsoup.Jsoup;
import org.jsoup.select.Elements;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.w3c.dom.Document;
import org.w3c.dom.NodeList;

import at.fhj.antesk.steamvaluecalc.interfaces.Retriever;
import at.fhj.antesk.steamvaluecalc.resources.Configs;
import at.fhj.antesk.steamvaluecalc.utils.HelperUtil;
import at.fhj.antesk.steamvaluecalc.view.Overview;

public class GameDataRetriever implements Retriever {

	private Map<String, String[]> csvMap = Configs.getInstance().getCsvMap();
	private Map<String, String> configsMap = Configs.getInstance().getConfigsMap();

	private DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();

	private static Logger logger = LoggerFactory.getLogger(GameDataRetriever.class);

	private java.sql.Connection connection = new DatabaseAccess().getConnection();
	private Overview mainFrame;
	private Calendar calendar = Calendar.getInstance();

	public Game retrieveDataForGame(Game game) throws IOException {
		Connection connection = Jsoup.connect(game.getStoreLink());
		Elements allElements = connection.get().body().getAllElements();
		String priceTag = "";

		if (allElements.hasClass("apphub_StorePrice")) {
			Elements priceElement = connection.get().body().getElementsByClass("price");
			Elements discountFinalPriceElement = connection.get().body().getElementsByClass("discount_final_price");
			Elements discountOriginalPriceElement = connection.get().body().getElementsByClass("discount_original_price");
			Elements discountPercentElement = connection.get().body().getElementsByClass("discount_pct");

			if (priceElement.isEmpty()) {
				logger.debug("No price tag found for {} with ID {}. Trying parsing for discounted games...", game.getName(),
						game.getId());
				priceElement = discountFinalPriceElement;
			}

			priceTag = priceElement.text();
			String[] extractPriceAndCurrency = HelperUtil.splitAndExtractPriceAndCurrency(priceTag);

			game.setPrice(Double.parseDouble(extractPriceAndCurrency[0]));
			game.setCurrency(extractPriceAndCurrency[1]);

			if (!discountOriginalPriceElement.isEmpty()) {
				String discountOriginalPrice = discountOriginalPriceElement.text();
				String[] priceAndCurrency = HelperUtil.splitAndExtractPriceAndCurrency(discountOriginalPrice);

				game.setDiscountOriginalPrice(Double.parseDouble(priceAndCurrency[0]));
				game.setCurrency(priceAndCurrency[1]);
			}

			if (!discountPercentElement.isEmpty()) {
				String discountPercent = discountPercentElement.text();
				String[] split = discountPercent.split("%");
				game.setDiscountPercent(Integer.parseInt(split[0]));
			}

			game.setHourValue(HelperUtil.calculateHourValueForGame(game.getPrice(), game.getHoursOnRecord()));

			Timestamp lastChecked = new Timestamp(calendar.getTime().getTime());
			game.setLastChecked(lastChecked);
		} else {
			game.setPrice(null);
			logger.debug("No price tags of any description found. Assuming that {} is a free game.", game.getName());
		}

		return game;
	}

	public List<Game> retrieveGameListFromXml(String account) {
		List<Game> gameList = new ArrayList<Game>();

		try {
			DocumentBuilder documentBuilder = dbf.newDocumentBuilder();
			Document document;

			if (mainFrame.hasNoAlias()) {
				document = documentBuilder.parse("http://steamcommunity.com/profiles/" + account + "/games?tab=all&xml=1");
			} else {
				document = documentBuilder.parse("http://steamcommunity.com/id/" + account + "/games?tab=all&xml=1");
			}

			NodeList games = document.getElementsByTagName("game");

			for (int i = 0; i < games.getLength(); i++) {
				NodeList childNodes = games.item(i).getChildNodes();

				BigDecimal id = new BigDecimal(0);
				String name = "-";
				String logo = "-";
				String storeLink = "-";
				double hoursOnRecord = 0;

				for (int j = 0; j < childNodes.getLength(); j++) {
					if (childNodes.item(j).getNodeName().equals("appID")) {
						id = BigDecimal.valueOf(Integer.parseInt(childNodes.item(j).getTextContent()));
					} else if (childNodes.item(j).getNodeName().equals("name")) {
						name = childNodes.item(j).getTextContent();
					} else if (childNodes.item(j).getNodeName().equals("logo")) {
						logo = childNodes.item(j).getTextContent();
					} else if (childNodes.item(j).getNodeName().equals("storeLink")) {
						storeLink = childNodes.item(j).getTextContent();
					} else if (childNodes.item(j).getNodeName().equals("hoursOnRecord")) {
						hoursOnRecord = Double.parseDouble(childNodes.item(j).getTextContent());
					}
				}

				Game game = new Game(id, name, logo, storeLink, hoursOnRecord);
				gameList.add(game);
				logger.debug("{} added to gameList for account {}", name, account);
			}
		} catch (Exception e) {
			logger.error("Error occured while parsing game list.", e);
			String stackTrace = ExceptionUtils.getStackTrace(e);
			//			JOptionPane.showMessageDialog(mainFrame, "An error occured during parsing of the game list:\n\n" + stackTrace
			//					+ "\nMake sure you are connected to the internet.", "Error", JOptionPane.ERROR_MESSAGE);
		}

		return gameList;
	}

	@Override
	public Account createAccount(String account) {
		Map<String, String> accountData = createAccountDataMap(account);

		Account result = new Account();
		if (!accountData.isEmpty()) {
			result.setId(BigDecimal.valueOf(Long.parseLong(accountData.get("accountId"))));
			result.setAvatarIconUrl(accountData.get("accountAvatarIconUrl"));
			result.setCustomUrl(accountData.get("accountCustomUrl"));
			result.setName(accountData.get("accountName"));
			result.setVisibilityState(Byte.parseByte(accountData.get("accountVisibilityState")));

			try {
				Statement statement = connection.createStatement();
				String sql = "INSERT INTO accounts VALUES(" + // 
						result.getId() + ", '" + //
						result.getName() + "', " + //
						result.getVisibilityState() + ", '" + // 
						result.getAvatarIconUrl() + "', '" + //
						result.getCustomUrl() + "', " + //
						"CURRENT_TIMESTAMP" + //
						")"; //
				statement.execute(sql);
			} catch (SQLException e) {
				logger.trace("Error occured while database was queried.", e);
				if (e.getSQLState().equals("23000")) {
					logger.info("Account key for {} already exists in database", account);
				}
			}
		}

		List<Game> gamesListFromXml = retrieveGameListFromXml(account);
		List<Game> gamesListFromDatabase = retrieveGameListFromDatabase(accountData.get("accountName"));

		List<Game> gameList = createGameListFromXmlAndDb(gamesListFromDatabase, gamesListFromXml);

		result.setGames(gameList);
		return result;
	}

	public List<Game> createGameListFromXmlAndDb(List<Game> dbList, List<Game> xmlList) {
		List<Game> gameList = new ArrayList<Game>();
		gameList.addAll(xmlList);

		List<Game> gamesToRemove = new ArrayList<Game>();
		List<Game> gamesToReAdd = new ArrayList<Game>();

		for (Game game : gameList) {
			BigDecimal gameId = game.getId();
			for (Game gameFromDb : dbList) {
				if (gameId.equals(gameFromDb.getId())) {
					gamesToRemove.add(game);
					gamesToReAdd.add(gameFromDb);
				}
			}
		}

		gameList.removeAll(gamesToRemove);
		gameList.addAll(gamesToReAdd);

		return gameList;
	}

	public List<Game> retrieveGameListFromDatabase(String account) {
		List<Game> gamesFromDatabase = new ArrayList<Game>();

		try {
			Statement statement = connection.createStatement();
			ResultSet resultSet = getGamesForAccountResultSet(account, statement);

			while (resultSet.next()) {
				BigDecimal id = resultSet.getBigDecimal("id");
				String name = resultSet.getString("name");
				String logo = resultSet.getString("logo");
				String storeLink = resultSet.getString("storeLink");
				Double hoursOnRecord = resultSet.getDouble("hoursOnRecord");
				String price = resultSet.getString("price");
				String currency = resultSet.getString("currency");
				Integer discountPercent = resultSet.getInt("discountPercent");
				Double discountOriginalPrice = resultSet.getDouble("discountOriginalPrice");
				Timestamp lastChecked = resultSet.getTimestamp("lastChecked");

				String pricePayed = resultSet.getString("pricePayed");

				String currencyUsed = resultSet.getString("currencyUsed");

				if (currencyUsed == null) {
					currencyUsed = currency;
					if (currencyUsed == null) {
						currencyUsed = "";
					}
				}
				if (pricePayed == null) {
					pricePayed = price;
					if (pricePayed == null) {
						pricePayed = "0.0";
					}
				}

				double hourValueForGame = HelperUtil.calculateHourValueForGame(Double.parseDouble(pricePayed), hoursOnRecord);

				Game game = new Game(id, name, logo, storeLink, hoursOnRecord, Double.parseDouble(pricePayed), hourValueForGame,
						currencyUsed, discountPercent, discountOriginalPrice, lastChecked);
				gamesFromDatabase.add(game);
			}
		} catch (SQLException e) {
			logger.error("Error retrieving game list from database: ", e);
		}

		return gamesFromDatabase;
	}

	private ResultSet getGamesForAccountResultSet(String account, Statement statement) throws SQLException {
		//@formatter:off
		return statement.executeQuery("SELECT " + 
				"g.id AS \"id\", " + 
				"g.name AS \"name\", " + 
				"g.logo_url AS \"logo\", " + 
				"g.store_url AS \"storeLink\", " + 
				"o.hours as \"hoursOnRecord\", " + 
				"(SELECT " + 
				"	price "	+ 
				"FROM prices pr " + 
				"WHERE g.id = pr.game_id " + 
				"ORDER BY last_checked DESC " + 
				"LIMIT 1) AS \"price\",	" + 
				"(SELECT " + 
				"	currency " + 
				"FROM prices " + 
				"WHERE g.id = game_id " + 
				"ORDER BY last_checked DESC " + 
				"LIMIT 1) AS \"currency\", " + 
				"(SELECT " + 
				"	discount_percent " + 
				"FROM prices " + 
				"WHERE g.id = game_id " + 
				"ORDER BY last_checked DESC	" + 
				"LIMIT 1) AS \"discountPercent\", " + 
				"(SELECT " + 
				"	discount_original_price " + 
				"FROM prices " + 
				"WHERE g.id = game_id " + 
				"ORDER BY last_checked DESC " + 
				"LIMIT 1 ) AS \"discountOriginalPrice\", " + 
				"(SELECT " + 
				"	last_checked " + 
				"FROM prices " + 
				"WHERE g.id = game_id " + 
				"ORDER BY last_checked DESC " + 
				"LIMIT 1) AS \"lastChecked\", " + 
				"o.price AS \"pricePayed\", " + 
				"o.currency AS \"currencyUsed\" " + 
				"FROM accounts a " + 
				"LEFT OUTER JOIN ownership o ON a.id = o.profile_id " + 
				"LEFT OUTER JOIN games g ON g.id = o.game_id " + 
				"LEFT OUTER JOIN prices p ON g.id = p.game_id " + 
				"WHERE a.name = '" + account + "' " + 
				"GROUP BY g.name");
		//@formatter:on
	}

	public Map<String, String> createAccountDataMap(String account) {
		Map<String, String> map = new HashMap<String, String>();

		try {
			DocumentBuilder documentBuilder = dbf.newDocumentBuilder();
			Document document;

			if (mainFrame.hasNoAlias()) {
				document = documentBuilder.parse("http://steamcommunity.com/profiles/" + account + "/?xml=1");
			} else {
				document = documentBuilder.parse("http://steamcommunity.com/id/" + account + "/?xml=1");
			}

			NodeList steamID64 = document.getElementsByTagName("steamID64");
			NodeList steamID = document.getElementsByTagName("steamID");
			NodeList visibilityState = document.getElementsByTagName("visibilityState");
			NodeList avatarIcon = document.getElementsByTagName("avatarFull");
			NodeList customURL = document.getElementsByTagName("customURL");

			String accountId = steamID64.item(0).getTextContent();
			String accountName = steamID.item(0).getTextContent();
			String accountVisibilityState = visibilityState.item(0).getTextContent();
			String accountAvatarIconUrl = avatarIcon.item(0).getTextContent();
			String accountCustomUrl = customURL.item(0).getTextContent();

			map.put("accountId", accountId);
			map.put("accountName", accountName);
			map.put("accountVisibilityState", accountVisibilityState);
			map.put("accountAvatarIconUrl", accountAvatarIconUrl);
			map.put("accountCustomUrl", accountCustomUrl);
		} catch (Exception e) {
			logger.error("Error occured while parsing account data.", e);
		}

		return map;
	}

	@Override
	public List<Game> retrievePricesForGameList(List<Game> gameList, JProgressBar progressBar) throws IOException {
		int counter = 0;
		for (Game game : gameList) {
			game = retrieveDataForGame(game);
			progressBar.setValue(counter + 1);
			logger.debug("Price set for game:" + game.getName() + " to " + game.getPrice() + " " + game.getCurrency()
					+ " Discounts: " + game.getDiscountPercent() + " % - Original Price: " + game.getDiscountOriginalPrice());
			insertIntoDatabase(game);
			counter++;
		}

		return gameList;
	}

	public void insertIntoDatabase(Game game) {
		// Prepare fields
		BigDecimal id = game.getId();
		String name = game.getName();
		name = name.replace("'", "\\'");
		String logo = game.getLogo();
		String storeLink = game.getStoreLink();

		Double hoursOnRecord = game.getHoursOnRecord();
		String hours = "null";
		if (hoursOnRecord != null) {
			hours = String.valueOf(hoursOnRecord.doubleValue());
		}

		Double price = game.getPrice();
		String priceString = "null";
		if (price != null) {
			priceString = String.valueOf(price.doubleValue());
		}

		String currency = "'" + game.getCurrency() + "'";
		if (game.getCurrency() == null) {
			currency = "null";
		}

		Integer discountPercent = game.getDiscountPercent();
		String discountP = "null";
		if (discountPercent != null) {
			discountP = String.valueOf(discountPercent.doubleValue());
		}

		Double discountOriginalPrice = game.getDiscountOriginalPrice();
		String discountOP = "null";
		if (discountOriginalPrice != null) {
			discountOP = String.valueOf(discountOriginalPrice.doubleValue());
		}

		//@formatter:off
		String insertGame = "INSERT INTO games VALUES (" +
				id + ", " +
				"'" + name + "', " +
				"'" + logo + "', " +
				"'" + storeLink + "'" +
				")";
		String insertPrice = "INSERT INTO prices VALUES(" +
				"null, " +
				id + ", " +
				priceString + ", " +
				currency + ", " +
				discountP + ", " +
				discountOP + ", " +
				"CURRENT_TIMESTAMP" +
				")";
		String insertOrUpdateOwnership = "INSERT INTO ownership VALUES (" +
				Overview.getAccount().getId() + ", " +
				id + ", " +
				hours + ", " +
				"null, " +
				"null, " +
				"CURRENT_TIMESTAMP) ON DUPLICATE KEY UPDATE " +
				"hours = " + hours + ", " +
				"price = null, " +
				"currency = null"; // TODO implement override functionality
		//@formatter:on

		Statement statement;
		try {
			statement = connection.createStatement();
			statement.execute(insertGame);
		} catch (SQLException e) {
			logger.debug("Error while writing games table.", e);
			if (e.getSQLState().equals("23000")) {
				logger.info("Entry for " + game.getName() + " already exists in database");
			}
		}
		try {
			statement = connection.createStatement();
			statement.execute(insertPrice);
		} catch (SQLException e) {
			logger.debug("Error while writing prices table.", e);
			if (e.getSQLState().equals("23000")) {
				logger.info("Entry for " + game.getName() + " already exists in database");
			}
		}
		try {
			statement = connection.createStatement();
			statement.execute(insertOrUpdateOwnership);
		} catch (SQLException e) {
			logger.debug("Error while writing ownership table.", e);
			if (e.getSQLState().equals("23000")) {
				logger.info("Entry for " + game.getName() + " already exists in database");
			}
		}
	}

	@Override
	public Map<String, String> getConfigsMap() {
		return configsMap;
	}

	@Override
	public Map<String, String[]> getCsvMap() {
		return csvMap;
	}

	@Override
	public Vector<String> createGameListForJList(List<Game> gameList) {
		Vector<String> result = new Vector<String>();

		for (Game game : gameList) {
			result.add(game.getName());
		}

		return result;
	}

	@Override
	public void setMainFrame(Overview mainFrame) {
		this.mainFrame = mainFrame;
	}

	public TableModel getPriceHistoryTableDataForGame(Game game) {
		TableModel model = new DefaultTableModel();
		String sql = "SELECT " + //
				"price, " + //
				"currency, " + //
				"discount_percent, " + //
				"discount_original_price, " + //
				"last_checked " + //
				"FROM prices " + //
				"WHERE game_id = " + game.getId(); //
		String[][] modelDataArray;

		try {
			Statement statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery(sql);
			modelDataArray = new String[HelperUtil.getRowCount(resultSet)][5];
			int counter = 0;

			resultSet.beforeFirst();
			while (resultSet.next()) {
				modelDataArray[counter][0] = resultSet.getString("price");
				modelDataArray[counter][1] = resultSet.getString("currency");
				modelDataArray[counter][2] = resultSet.getString("discount_percent");
				modelDataArray[counter][3] = resultSet.getString("discount_original_price");
				modelDataArray[counter][4] = resultSet.getString("last_checked");
				counter++;
			}

			String[] modelColumnsArray = csvMap.get("price.history.table.headers");
			model = new DefaultTableModel(modelDataArray, modelColumnsArray);
		} catch (SQLException e) {
			logger.error("Error retrieving price history data for {}", game.getName(), e);
		}

		return model;
	}

	@Override
	public void updateHoursInDatabase(Game game, Account account) {
		String sql = "UPDATE ownership SET hours = " + game.getHoursOnRecord() + " WHERE profile_id = " + account.getId()
				+ " AND game_id = " + game.getId();

		try {
			Statement statement = connection.createStatement();
			statement.execute(sql);
			logger.debug("Hours for " + game.getName() + " set to " + game.getHoursOnRecord() + " for account "
					+ account.getName());
		} catch (SQLException e) {
			logger.error("Error updating table ownership for {} with account {}.", game.getName(), account.getName());
			logger.error("Exception: ", e);
		}

	}

	@Override
	public void updatePriceInDatabase(Game game, Account account) {
		String sql = "UPDATE ownership SET price = " + game.getPrice() + " WHERE profile_id = " + account.getId()
				+ " AND game_id = " + game.getId();

		try {
			Statement statement = connection.createStatement();
			statement.execute(sql);
			logger.debug("Price for " + game.getName() + " set to " + game.getPrice() + " for account " + account.getName());
		} catch (SQLException e) {
			logger.error("Error updating table ownership for {} with account {}.", game.getName(), account.getName());
			logger.error("Exception: ", e);
		}
	}

}
